package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.People;
import by.bsu.touragency.db.connection.DBConnector;

public class PeopleDAO extends AbstractDAO {

	public static final String SQL_PEOPLE_CREATE = " INSERT INTO tourAgencyDB.people (adults, children) VALUES (? , ?);";
	public static final String SQL_PEOPLE_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.people WHERE adults = ? AND children = ? ;";
	public static final String SQL_PEOPLE_IS_USED = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.hotel WHERE pepleId = ? ;";
	public static final String SQL_PEOPLE_IS_EXIST = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.people WHERE adults = ? AND children = ? ;";
	public static final String SQL_PEOPLE_READ_BY_ID = "SELECT * FROM tourAgencyDB.people WHERE (id= ?);";
	public static final String SQL_PEOPLE_DELETE = "DELETE FROM tourAgencyDB.people WHERE id = ?";

	private static Logger logger = Logger.getLogger("log");

	private boolean isUsed(int id) {
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_PEOPLE_IS_USED);
			int n;
			try {// 3
				statement.setLong(1, id);
				resultSet = statement.executeQuery();
				n = -2;
				resultSet.next();
				n = resultSet.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			return false;
		} catch (Exception e) {
			return false;
		}
	}

	public boolean isExistByType(People people) {

		logger.info("Is exist this type of \"people\"");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_PEOPLE_IS_EXIST);
			int n;
			try {// 3
				statement.setInt(1, people.getAdults());
				statement.setInt(2, people.getChildren());
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet2 != null) {
				resultSet2.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n == 0) {
				return false;
			} else {
				return true;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}
	}

	// C
	public People create(People people) {
		logger.info("Create people");
		try {

			if (!isExistByType(people)) {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_PEOPLE_CREATE);
				statement.setLong(1, people.getAdults());
				statement.setLong(2, people.getChildren());
				statement.executeUpdate();

			}
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_PEOPLE_CREATE_GET_ID);
			statement.setLong(1, people.getAdults());
			statement.setLong(2, people.getChildren());
			resultSet = statement.executeQuery();

			if (resultSet.next()) {
				people.setId(resultSet.getInt("id"));
			}

			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return people;
	}

	// R
	public People readById(int id) {
		logger.info("Read people by id");
		People people = new People();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_PEOPLE_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				people.setId(resultSet.getInt("id"));
				people.setAdults(resultSet.getInt("adults"));
				people.setChildren(resultSet.getInt("children"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return people;
	}

	public People readByChildrenAndAdults(int children, int adults) {
		logger.info("Read people by number of children&adults");
		People people = new People();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_PEOPLE_CREATE_GET_ID);
			statement.setInt(1, adults);
			statement.setInt(2, children);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				people.setId(resultSet.getInt("id"));
				people.setAdults(resultSet.getInt("adults"));
				people.setChildren(resultSet.getInt("children"));
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return people;
	}

	// D
	public void deleteById(int id) {
		if (!isUsed(id)) {
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_PEOPLE_DELETE);
				statement.setInt(1, id);
				statement.executeUpdate();
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
	}
}
